package com.yangxk.kadmin.web.module.gen.util;

import com.yangxk.kadmin.common.exception.BizException;
import com.yangxk.kadmin.generator.field.Field;
import com.yangxk.kadmin.web.common.enumeration.ResponseCode;
import com.yangxk.kadmin.web.module.gen.entity.TableColumn;
import com.yangxk.kadmin.web.module.gen.vo.ShowTable;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DbUtil {

    public static Connection getConnection(String driver, String url, String username, String password) throws Exception {
        Class.forName(driver);
        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password);
        props.setProperty("useInformationSchema", "true");
        props.setProperty("remarks", "true");
        Connection connection = DriverManager.getConnection(url, props);
        return connection;
    }

    public static Connection getConnection(DataSource dataSource) {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("获取数据库连接失败");
        }
    }

    public static void close(Object o) {
        if (o == null)
            return ;
        if (o instanceof ResultSet) {
            try {
                ((ResultSet)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (o instanceof Statement) {
            try {
                ((Statement)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (o instanceof Connection) {
            try {
                ((Connection)o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs, Statement statement) {
        close(rs);
        close(statement);
    }

    public static List<ShowTable> getTables(Connection connection) throws SQLException {
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            ShowTable showTable = null;
            List<ShowTable> result = new ArrayList<>();
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String remarks = rs.getString("REMARKS");       //表备注

                // 排除sys_ 和 gen_ 开头的表
                if (tableName.startsWith("sys_") || tableName.startsWith("gen_"))
                    continue;

                showTable = new ShowTable();
                showTable.setTableName(tableName);
                showTable.setComment(remarks);
                result.add(showTable);
            }

            return result;
        } finally {
            close(rs);
        }
    }

    public static String getPrimaryKey(Connection connection, String tableName) throws SQLException {
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            rs = metaData.getPrimaryKeys(null, null, tableName);
            String pk = "";
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                short keySeq = rs.getShort("KEY_SEQ");

                // 不支持联合主键
                if (keySeq > 1)
                    throw new BizException(ResponseCode.NOT_SUPPORT_COMPOSITE_KEY);

                pk = columnName;
            }

            return pk;
        } finally {
            close(rs);
        }
    }

    public static List<Field> getColumns(Connection connection, String tableName) throws SQLException {
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            rs = metaData.getColumns(null, null, tableName, null);
//            ResultSetMetaData rsmd = rs.getMetaData();
//            int columnCount = rsmd.getColumnCount();
//            for (int i = 1; i < columnCount; i++) {
//                // 不支持主键列自增
//                if ("IS_AUTOINCREMENT".equals(rsmd.getColumnName(i))) {
//                    System.out.println(rsmd.getColumnName(i));
//                    throw new BizException(ResponseCode.NOT_SUPPORT_PK_AUTOINCREMENT);
//                }
//            }
            List<TableColumn> list = new ArrayList<>();
            TableColumn tc = null;
            while (rs.next()) {
                tc = new TableColumn();
                tc.setColumnName(rs.getString("COLUMN_NAME"));
                tc.setColumnSize(rs.getInt("COLUMN_SIZE"));
                tc.setDataType(rs.getInt("DATA_TYPE"));
                tc.setRemarks(rs.getString("REMARKS"));

                list.add(tc);
            }

            String primaryKey = getPrimaryKey(connection, tableName);
            return GenUtil.transformColumn2Show(list, primaryKey);
        } finally {
            close(rs);
        }
    }

    public static void deleteById(Connection connection, String tableName, String id) {
        String sql = "delete from " + tableName + " where id = ?";
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("删除失败");
        } finally {
            DbUtil.close(ps);
        }

    }
}
